#需要安装  pip install pyexcel-xls
#然后填写表地址及储存地址
import xlrd
import codecs
#表地址
data = xlrd.open_workbook('C:\\Users\\szz\\Desktop\\a.xlsm')
#存储地址
location="C:\\Users\\szz\\Desktop\\a"
#仅需填写表地址及存储地址

names = data.sheet_names()#所有表名
number_tables=len(names)#表的个数
field = ''
for j in range(2,number_tables):
    table = data.sheet_by_name(names[j])
    start = 7#
    end=table.nrows#每个表的行数
    name=table.cell_value(5, 2)
    print(names[j]+" "+name)
    print(end)
    sql = "DROP TABLE IF EXISTS `"+name+"`;\n"
    sql+="create table if not exists`"+name+"`(\n"
    for x in range(start, end):
        row = table.row_values(x)
        if x==start:
            namefist=row[2];
        if row[2]=='' or row[2]==field:
            continue
        field = row[2]
        if row[4]!='':
            three=int(row[4])
        sql=sql+"`"+row[2]+"` "+row[3]
        if row[4]!='':
            sql+="("+str(three)
            if row[5]!='' and row[3]=='decimal':
                sql+=", "+str(int(row[5]))
            sql+=") "
        if row[7]!='':
            sql+=" DEFAULT '"+str(int(row[7]))+"'"
        if row[3]=='varchar':
            sql+=" CHARACTER SET utf8 COLLATE utf8_general_ci "
        if row[6]!='':
            sql+=" "+row[6]+" NULL "
        if row[7]=='' and row[6]=='':
            sql+=" DEFAULT NULL "
        sql+=" COMMENT '"+row[1]+"',\n"
    sql=sql+" PRIMARY KEY (`"+namefist+"`) USING BTREE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;"
    file_name=location+"\\"+name+".sql"
    file =codecs.open(file_name,'w',"utf-8")
    file.write(sql)
print("完成")


